﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MagicBag.CRM.IDAL;
using MagicBag.CRM.Model;
using MagicBag.CRM.Common;

namespace MagicBag.CRM.SqlServerDAL
{
    public class RoleDAL : IRole
    {
        public int Insert(Role role)
        {
            string sql = "insert into [dbo].[Role] ([Name] ,[Remark]) values(@Name,@Remark)";
            SqlParameter[] paras = new SqlParameter[2];
            paras[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            paras[1] = new SqlParameter("@Remark", SqlDbType.NVarChar, 500);

            paras[0].Value = role.Name;
            paras[1].Value = role.Remark;

            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Delete(int id)
        {
            string sql = "delete from [dbo].[Role] where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@ID", SqlDbType.Int);
            paras[0].Value = id;
            using (SqlConnection conn = new SqlConnection(SqlHelper.permissionConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public int Update(Role role)
        {
            string sql = "update [dbo].[Role] set [Name]= @Name,[Remark]=@Remark where [ID]=@ID";
            SqlParameter[] paras = new SqlParameter[3];
            paras[0] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            paras[1] = new SqlParameter("@Remark", SqlDbType.VarChar, 500);
            paras[2] = new SqlParameter("@ID", SqlDbType.Int);

            paras[0].Value = role.Name;
            paras[1].Value = role.Remark;
            paras[2].Value = role.ID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.baseConnectionString))
            {
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, paras);
            }
        }

        public Role GetRole(int id)
        {
            throw new NotImplementedException();
        }

        public IList<Role> GetRoles()
        {
            IList<Role> list = new List<Role>();

            string sql = "select  [ID] ,[Name] ,[Remark]  FROM [dbo].[Role]";

            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.baseConnectionString, CommandType.Text, sql, null))
            {
                while (sdr.Read())
                {
                    Role role = new Role();
                    role.ID = sdr.GetInt32(0);
                    role.Name = sdr.GetString(1);
                    role.Remark = sdr.IsDBNull(2) ? null : sdr.GetString(2);
                    list.Add(role);
                }
            }
            return list;
        }
    }
}
